昨天我們從 Heroku 拿到了一個資料庫(database)並且創造了一個可以儲存草泥馬訓練資料的表格(table)，今天就要來試著透過 psycopg2 將表格一點一滴的填滿囉！
先來複習一下昨天的，為了要在 Heroku Postgres 資料庫中創造一個符合我們需求的表格，我們需要用 SQL 語法跟資料庫溝通，下面是創造表格的 SQL：
CREATE TABLE alpaca_training(
record_no serial PRIMARY KEY,
alpaca_name VARCHAR (50) NOT NULL,
training VARCHAR (50) NOT NULL,
duration INTERVAL NOT NULL,
date DATE NOT NULL
);
而 Python 透過 psycopg2 連接資料庫並傳達 SQL 指令：
In [1]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
create_table_query = '''CREATE TABLE alpaca_training(
record_no serial PRIMARY KEY,
alpaca_name VARCHAR (50) NOT NULL,
training VARCHAR (50) NOT NULL,
duration INTERVAL NOT NULL,
date DATE NOT NULL
);'''
cursor.execute(create_table_query)
conn.commit()
cursor.close()
conn.close()
SQL 的資料操作可分為四大類：存入(INSERT)、查詢(SELECT)、更新(UPDATE)、刪除(DELETE)。這幾種方法，又稱為 CRUD: create, read, update, delete。
首先，有了表格，弄清楚每一個欄位的資料型態之後，我們就可以開始存入資料囉。相同的，先來看看 SQL 的指令怎麼寫：
INSERT INTO alpaca_training (alpaca_name, training, duration, date)
VALUES ('吉姆', '肌力訓練', '1:30:00', '2019-09-24');
首先是一隻健壯的草泥馬吉姆，牠一次就進行了 1 個半小時的肌力訓練。於是乎，我們在alpaca_training這個表格裡面，按照alpaca_name、training、duration、date這些欄位依序放入'吉姆'、'肌力訓練'、1:30:00、2019-09-24這些資料。那麼要怎麼利用 psycopg2 連接到資料庫並傳達 SQL 的指令呢？
In [2]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
record = ('吉姆', '肌力訓練', '1:30:00', '2019-09-24')
table_columns = '(alpaca_name, training, duration, date)'
postgres_insert_query = f"""INSERT INTO alpaca_training {table_columns} VALUES (%s, %s, %s, %s);"""
cursor.execute(postgres_insert_query, record)
conn.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into alpaca_training")
cursor.close()
conn.close()
1 Record inserted successfully into alpaca_training
第九行： cursor.execute(postgres_insert_query, record)
這行程式碼說明我們要執行存入資料的指令。這邊我用了一個 Python 的技巧，叫做清單分解(decouple)。上面我先令record = ('吉姆', '肌力訓練', '1:30:00', '2019-09-24')，讓record成為含有 4 個項目的清單，接著在創造postgres_insert_query的時候，預備了 4 個字串變數(%s)的位置。最後，在執行這一行指令的時候，record裡面的 4 個項目就會依序放入postgres_insert_query當中 4 個字串變數的位置了。
第十行： conn.commit()
記得，使用 SQT 存入(INSERT)動作時，必須要再加上conn.commit()，資料庫才會真正執行該指令。因此我們的資料是在這一行程式碼被執行完之後，才進到alpaca_training的表格裡。
第十一行： count = cursor.rowcount
最後，可以用cursor.rowcount來查詢究竟放入了幾筆資料。
或許有人會問，'1:30:00'指的是什麼啊？是 1 天 30 小時又 0 分嗎？可以再表示得更清楚嗎？在alpaca_training當中，我們的duration這個欄位用的資料類型是INTERVAL，這類型的欄位在輸入資料的時候，除了可以用'1:30:00'之外，也接受像是'1 year 2 month'、'3 hours 20 minutes'等等像是在寫文章一樣的，對時間的表述方式。法蘭克看到吉姆做了 1 個半小時的肌力訓練，不甘示弱，一頭栽下去就進行了 1 個小時 35 分鐘的肌力訓練。
INSERT INTO alpaca_training (alpaca_name, training, duration, date)
VALUES ('法蘭克', '肌力訓練', '1 hours 35 minutes', '2019-09-24')
In [3]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
record = ('法蘭克', '肌力訓練', '1 hours 35 minutes', '2019-09-24')
table_columns = '(alpaca_name, training, duration, date)'
postgres_insert_query = f"""INSERT INTO alpaca_training {table_columns} VALUES (%s, %s, %s, %s);"""
cursor.execute(postgres_insert_query, record)
conn.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into alpaca_training")
cursor.close()
conn.close()
1 Record inserted successfully into alpaca_training
或是這樣寫'1 hours 35 minutes'覺得太囉嗦，想將時間單位縮寫，INTERVAL 的欄位也願意接受：
INSERT INTO alpaca_training (alpaca_name, training, duration, date)
VALUES ('藍道', '肌力訓練', '1 h 37 m', '2019-09-24')
In [4]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
record = ('藍道', '肌力訓練', '1 h 37 m', '2019-09-24')
table_columns = '(alpaca_name, training, duration, date)'
postgres_insert_query = f"""INSERT INTO alpaca_training {table_columns} VALUES (%s, %s, %s, %s);"""
cursor.execute(postgres_insert_query, record)
conn.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into alpaca_training")
cursor.close()
conn.close()
1 Record inserted successfully into alpaca_training
我們當然可以這樣一筆一筆資料慢慢放進去。但是想想我養的草泥馬的數量，就忽然覺得這不是太有效率的手段，怎麼辦呢？沒關係，psycopg2 提供了cursor.executemany()的方法，讓我們可以一次傳達多個命令。
In [5]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
records = [('大衛', '肌力訓練', '1:00:00', '2019-09-24'),
('威廉', '肌力訓練', '1:00:00', '2019-09-24'),
('彼得', '肌力訓練', '1:00:00', '2019-09-24'),
('大衛', '咬合訓練', '1:00:00', '2019-09-24'),
('威廉', '咬合訓練', '1:00:00', '2019-09-24'),
('彼得', '咬合訓練', '1:00:00', '2019-09-24'),
('大衛', '牧草訓練', '1:30:00', '2019-09-24'),
('威廉', '牧草訓練', '1:30:00', '2019-09-24'),
('彼得', '牧草進食', '1:30:00', '2019-09-24')]
table_columns = '(alpaca_name, training, duration, date)'
postgres_insert_query = f"""INSERT INTO alpaca_training {table_columns} VALUES (%s,%s,%s,%s)"""
cursor.executemany(postgres_insert_query, records)
conn.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into alpaca_training")
cursor.close()
conn.close()
9 Record inserted successfully into alpaca_training
原本我們用record將資料放進 tuple 當中。現在用cursor.executemany()，則將代表一筆資料的 tuple 整理好一起放入 list 內。最後用cursor.rowcount做查詢時，就會發現，原來我們真的一次存入了 9 筆資料啊。一次操作多筆資料，就是這麼簡單！
雖然說每次存入資料，我們都用cursor.rowcount做確認，但果然還是放心不下。所謂眼見為憑，有沒有什麼方法可以顯示我們存入的資料呢？SQL 提供了查詢(SELECT)的關鍵字，讓我們可以從表格裡面讀出資料，甚至按照指定的要求選擇要呈現的資料。
SELECT * FROM alpaca_training
這是最簡單的一項指令，翻譯出來就是：「從alpaca_training這個表格選取所有欄位的資料」。因為是選擇所有欄位，所以用*來代表。用 psycopg2 來執行看看吧：
In [6]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM alpaca_training"""
cursor.execute(postgres_select_query)
接著，為了要顯示cursor裡面抓取出來的資料，我們可以用fetchone()、fetchmany()、跟fetchall()。
In [7]: cursor.fetchone()
Out[7]: (1, '吉姆', '肌力訓練', datetime.timedelta(seconds=5400), datetime.date(2019, 9, 24))
我們昨天已經稍微介紹過fetchone()了。顧名思義，fetchone()會從cursor當中抓出一筆資料，所以每執行一次fetchone()，cursor當中的資料就會少一筆。一筆資料就是一個 tuple，而不同欄位的資訊則是 tuple 中的項目。
In [8]: cursor.fetchmany(3)
Out[8]: [(2, '法蘭克', '肌力訓練', datetime.timedelta(seconds=5700), datetime.date(2019, 9, 24)),
(3, '藍道', '肌力訓練', datetime.timedelta(seconds=5820), datetime.date(2019, 9, 24)),
(4, '大衛', '肌力訓練', datetime.timedelta(seconds=3600), datetime.date(2019, 9, 24))]
當然，我們也可以用fetchmany()一次抓取特定筆數的資料。相同的，一筆資料就是一個 tuple，而fetchmany()會將一筆一筆的資料整理起來放進 list 回傳給你。
In [9]: cursor.fetchall()
Out[9]: [(5, '威廉', '肌力訓練', datetime.timedelta(seconds=3600), datetime.date(2019, 9, 24)),
(6, '彼得', '肌力訓練', datetime.timedelta(seconds=3600), datetime.date(2019, 9, 24)),
(7, '大衛', '咬合訓練', datetime.timedelta(seconds=3600), datetime.date(2019, 9, 24)),
(8, '威廉', '咬合訓練', datetime.timedelta(seconds=3600), datetime.date(2019, 9, 24)),
(9, '彼得', '咬合訓練', datetime.timedelta(seconds=3600), datetime.date(2019, 9, 24)),
(10, '大衛', '牧草訓練', datetime.timedelta(seconds=5400), datetime.date(2019, 9, 24)),
(11, '威廉', '牧草訓練', datetime.timedelta(seconds=5400), datetime.date(2019, 9, 24)),
(12, '彼得', '牧草進食', datetime.timedelta(seconds=5400), datetime.date(2019, 9, 24))]
最後，我們直接了當地用fetchall()將還放在cursor中的資料一次拿出來。和fetchmany()一樣，一筆資料由 tuple 表示，而一筆一筆的資料則用 list 包裝起來。原來我們真的存入了 12 筆資料啊。
為什麼最後幾筆資料都是'大衛'、'威廉'、'彼得'、'大衛'、'威廉'、'彼得'，這樣子不斷重複呢？連訓練的內容都一模一樣，是不是我快想不到還能瞎掰什麼訓練資料了呢？當然不是。這是因為這三隻草泥馬是最新加入的夥伴，而面對還沒受過訓練的草泥馬，我都會從最基礎的菜單開始，先增強肌力，並慢慢提升牠們的食慾。因此菜單都是固定的('肌力訓練', '1:00:00')、('咬合訓練', '1:00:00')、以及('牧草進食', '1:30:00')。
誒，好像有個地方怪怪的？我們看仔細一點：
SELECT * FROM alpaca_training
WHERE traing = '牧草進食'
翻譯一下：我們只拿training欄位中內容為'牧草進食'的資料，並且顯示出該筆資料的所有欄位(*)。
In [10]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
training = '牧草進食'
postgres_select_query = f"""SELECT * FROM alpaca_training WHERE training = %s"""
cursor.execute(postgres_select_query, (training,))```
In [11]: cursor.fetchall()
Out[11]: [(12, '彼得', '牧草進食', datetime.timedelta(seconds=5400), datetime.date(2019, 9, 24))]
結果只有'彼得'，'大衛'跟'威廉'的資料跑哪裡去了呢？這次我想要一次調10、11兩筆資料出來，可是executemany()並沒有用在SELECT這個指令上，怎麼辦呢？
SELECT * FROM alpaca_training
WHERE record_no IN (10, 11)
沒關係，我們可以這樣下 SQL 的指令：
In [12]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
record_no = (10, 11)
postgres_select_query = f"""SELECT * FROM alpaca_training WHERE record_no IN %s"""
cursor.execute(postgres_select_query, (record_no,))
然後再用fetchall()抓出來看看我們找到什麼。
In [13]: cursor.fetchall()
Out[13]: [(10, '大衛', '牧草訓練', datetime.timedelta(seconds=5400), datetime.date(2019, 9, 24)),
(11, '威廉', '牧草訓練', datetime.timedelta(seconds=5400), datetime.date(2019, 9, 24))]
原來真的把'牧草進食'打成'牧草訓練'啦！糟糕。
還好 SQL 有提供更新(UPDATE)的操作，讓我們可以更改已經記錄在表格中資料。不囉嗦，直接來看段程式碼：
UPDATE alpaca_training
SET training = '牧草進食'
WHERE training = '牧草訓練'
上面那段指令，可以很輕易地就將我們不小心輸入錯誤的訓練名稱從'牧草訓練'更改為'牧草進食'，好喔，讓我們來試試：
In [14]: import os
import psycopg2
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
training = '牧草訓練'
postgres_update_query = f"""UPDATE alpaca_training SET training = '牧草進食' WHERE training = %s"""
cursor.execute(postgres_update_query, (training,))
conn.commit()
count = cursor.rowcount
print(count, "Record updated successfully into alpaca_training")
2 Record updated successfully into alpaca_training
太好了，兩筆登記錯誤的資料成功更新了。
SQL 提供的最後一種資料操作，是刪除(DELETE)。功能？沒錯，就跟大家想的一樣，把表格裡面的資料刪除。按照 SQL 的語法寫起來會像是：
DELETE FROM table
WHERE condition
舉例來說，如果我想刪除record_no為12的資料：
DELETE FROM alpaca_table
WHERE record_no = 12
不過我只是舉個例子而已，並沒有真的想刪除，所以就不示範怎麼用 psycopg2 向資料庫傳達指令囉？今天的內容蠻大一部分是參考網路上關於 psycopg2 的教學，大家有興趣可以點過去看看➀。今天我們把資料庫提供的各種方法都試著操作了一遍，明天就可以試著讓資料庫跟 LINE 聊天機器人互相連接囉。今天的內容若有不清楚的地方，歡迎直接在下面留言，我會盡可能地回覆大家的。謝謝大家！
➀ Psycopg2 使用教學
➁ PostgreSQL 使用教學
➂ PostgreSQL 中文使用手冊
註：對於此系列文有興趣的讀者，歡迎參考由此系列文擴編成書的 LINE Bot by Python，以及最新的系列文《賴田捕手：追加篇》
第 31 天 初始化 LINE BOT on Heroku
第 32 天 快速回覆 QuickReply 介紹
第 33 天 妥善運用 Heroku APP 暫存空間
第 34 天 妥善運用 LINE Notify 免費推播
第 35 天 製造 Deploy to Heroku 按鈕
請問有沒有試過用LocationSendMessage? 如何可以將floating point 参數從database 傳到函數?
您好：
之前沒有用過LocationSendMessage這個函式，最近也還沒時間嘗試。參考了line-bot-sdk的官方文件：
location_message = LocationSendMessage(
title='my location',
address='Tokyo',
latitude=35.65910807942215,
longitude=139.70372892916203
)
知道要使用LocationSendMessage需要準備latitude跟longitude兩個浮點數資料。
假設您的問題是，如何將浮點數資料存到 Heroku Postgres 再拿出來嗎？
若是這樣的話，我們可以先在資料庫中準備一個新的表格：
CREATE TABLE IF NOT EXISTS location (
id serial PRIMARY KEY,
title VARCHAR NOT NULL,
address VARCHAR NOT NULL,
latitude NUMERIC (9, 6) NOT NULL,
longitude NUMERIC (9, 6) NOT NULL
);
利用上面的 SQL 語法應該可以做出一個名為location的表格，該表格中有latitude跟longitude這兩個欄位負責儲存經緯度的浮點數資料。
其中，NUMERIC (9, 6)表示我們預定存進的浮點數資料最多總共允許 9 個數字，而小數點以下的位數設定為 6 位。所以說，35.65910807942215存進表格後，會成為35.659108，而139.70372892916203則會成為139.703729。
有了表格之後，下一步就是存資料：
# 表格的欄位名稱
location_columns = 'title, address, latitude, longitude'
# 存資料所用的 SQL 語法
postgres_insert_query = f"""INSERT INTO location ({location_columns}) VALUES (%s,%s,%s,%s)"""
# 準備存入的資料
data_to_insert = ('my location', 'tokyo', 35.65910807942215, 139.70372892916203)
# 存資料
cursor.execute(postgres_insert_query, data_to_insert)
conn.commit()
最後，拿出來看看我們存進去的資料：
# 提取資料所用的 SQL 語法
postgres_select_query = f"""SELECT * from location"""
# 提取資料
cursor.execute(postgres_select_query)
dataclip = cursor.fetchone()
# 查看我們提取出來的資料
print('dataclip:', dataclip)
print('latitude:', float(dataclip[3]))
print('longitude:', float(dataclip[4]))
dataclip: (1, 'my location', 'tokyo', Decimal('35.659108'), Decimal('139.703729'))
latitude: 35.659108
longitude: 139.703729
我想應該可以得到如上述的結果。用float()轉換成 Python 的浮點數物件，再放進LocationSendMessage應該就可以用了？
我覺得大略的流程應該是這樣，不過沒有實際用過。若這是您想討論的應用，那可以這麼試試看，如果有其他狀況，可以再留言討論！
iThome鐵人賽
看影片追技術